package me.liangjun.patient.repository;

import me.liangjun.patient.domain.Patient;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

/**
* @website https://el-admin.vip
* @author wei
* @date 2021-09-05
**/
public interface PatientRepository extends JpaRepository<Patient, Long>, JpaSpecificationExecutor<Patient> {

    @Query(value = "select age_temp,COUNT(*) as total from (\n" +
            "select age,\n" +
            "case\n" +
            "when age between 11 and 20 then '11-20岁'\n" +
            "when age between 21 and 30 then '21-30岁'\n" +
            "when age between 31 and 40 then '31-40岁'\n" +
            "when age between 41 and 50 then '41-50岁'\n" +
            "when age between 51 and 60 then '51-60岁'\n" +
            "when age between 61 and 70 then '61-70岁'\n" +
            "when age between 71 and 80 then '71-80岁'\n" +
            "end as age_temp\n" +
            "from patient\n" +
            ") t_user group by age;",nativeQuery = true)
    Object[] statistics3();

    @Query(value = "SELECT DATE_FORMAT(join_time,'%Y-%m') as month,count(*) as num from patient\n" +
            "where  DATE_FORMAT(join_time,'%Y-%m')> DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') \n" +
            "group by DATE_FORMAT(join_time,'%Y-%m')",nativeQuery = true)
    List<Tuple> findGroupByJoinTime();

    @Query(value = "SELECT province,count(*) as num from patient group by province", nativeQuery = true)
    List<Tuple> findGroupByProvince();
}